package drugdealer.persistencia;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Vector;

import drugdealer.dados.Lote;
import drugdealer.dados.Remedio;
import drugdealer.persistencia.exceptions.AlreadyExistsException;
import drugdealer.persistencia.exceptions.DoesNotExistsException;

public class EstoquePersistence extends TablePersistence implements IEstoquePersistence {
	public static String TABLENAME = "estoque";
	public static String PRIMARYKEY = "codigo";
	
	public EstoquePersistence(DDPersistence owner) {
		super(owner);
		this.tableName = EstoquePersistence.TABLENAME;
		this.primaryKey = EstoquePersistence.PRIMARYKEY;
		this.createTableStmt =
			"CREATE TABLE IF NOT EXISTS " + EstoquePersistence.TABLENAME + " (" +
			EstoquePersistence.PRIMARYKEY + " integer NOT NULL, " +
			"cod_remedio integer NOT NULL, " +
			"datafabricacao varchar(10), " +
			"datavalidade varchar(10), " +
			"qtdporlote integer, " +
			"qtdestoque integer, " +
			"PRIMARY KEY (" + EstoquePersistence.PRIMARYKEY + ",cod_remedio), " + 
			"FOREIGN KEY (cod_remedio) REFERENCES " +
			EstoquePersistence.TABLENAME + "(" + EstoquePersistence.PRIMARYKEY +
			") ON DELETE CASCADE" +
			")";
	}
	
	/* (non-Javadoc)
	 * @see drugdealer.persistencia.IEstoquePersistence#get(int)
	 */
	public Lote get(int codigo) {
		Lote lote = null;
		String selectLote = "SELECT * FROM " + EstoquePersistence.TABLENAME + 
				" WHERE " + EstoquePersistence.PRIMARYKEY + "=" + codigo ;
		
		try {
			Statement s = this.ddc.createStatement();
			ResultSet rs = s.executeQuery(selectLote);
			if (rs.next()) {
				DateFormat formato = new SimpleDateFormat("dd/MM/yyyy");
				lote = new Lote(
						rs.getInt(EstoquePersistence.PRIMARYKEY),
						formato.parse(rs.getString("datafabricacao")),
						formato.parse(rs.getString("datavalidade")),
						rs.getInt("qtdestoque"), rs.getInt("qtdporlote"),
						(Remedio) this.ddc.get(
								DDPersistence.REMEDIO,rs.getInt("cod_remedio")
						)
				);
			}
			rs.close ();
			s.close ();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		}

		return lote;
	}
	
	/* (non-Javadoc)
	 * @see drugdealer.persistencia.IEstoquePersistence#getList(java.lang.String)
	 */
	public Vector<String[]> getList(String remedioName) {
		Vector<String[]> list = new Vector<String[]>();
		String selectList = "SELECT r.nome,e." + EstoquePersistence.PRIMARYKEY +
				" FROM " + EstoquePersistence.TABLENAME + " e, " +
				RemedioPersistence.TABLENAME + " r" +
				" WHERE r." + EstoquePersistence.PRIMARYKEY + "=e.cod_remedio" +
				" ORDER BY r.nome";

		System.out.println(selectList);

		String[] pair;
		try {
			Statement s = this.ddc.createStatement();
			ResultSet rs = s.executeQuery(selectList);
			
			while (rs.next()) {
				pair = new String[2];
				pair[0] = rs.getString(EstoquePersistence.PRIMARYKEY);
				pair[1] = rs.getString("nome") + " (cod: " + pair[0] + ")";
				list.add(pair);
			}
			rs.close ();
			s.close ();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	/* (non-Javadoc)
	 * @see drugdealer.persistencia.IEstoquePersistence#getList(drugdealer.dados.Remedio)
	 */
	public Vector<Lote> getList(Remedio remedio) {
		Vector<Lote> list = new Vector<Lote>();
		String selectList = "SELECT * FROM " + EstoquePersistence.TABLENAME +
				" WHERE cod_remedio=" + remedio.getCodigo() +
				" ORDER BY qtdporlote,qtdestoque DESC";

		System.out.println(selectList);

		try {
			Statement s = this.ddc.createStatement();
			ResultSet rs = s.executeQuery(selectList);
			while (rs.next()) {
				DateFormat formato = new SimpleDateFormat("dd/MM/yyyy");
				list.add(new Lote(rs.getInt("codigo"),
						formato.parse(rs.getString("datafabricacao")),
						formato.parse(rs.getString("datavalidade")),
						rs.getInt("qtdporlote"), rs.getInt("qtdestoque"),
						remedio));
			}
			rs.close ();
			s.close ();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ParseException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	/* (non-Javadoc)
	 * @see drugdealer.persistencia.IEstoquePersistence#add(drugdealer.dados.Lote)
	 */
	public void add(Lote lote) throws AlreadyExistsException {
		SimpleDateFormat formato = new SimpleDateFormat("dd/MM/yyyy");
		super.add("INSERT INTO " + EstoquePersistence.TABLENAME +
				" VALUES (" + lote.getCodigo() + ", " +
				lote.getRemedio().getCodigo() + ", '" + 
				formato.format(lote.getDataFabricacao()) + "', '" +
				formato.format(lote.getDataValidade()) + "', " +
				lote.getQtdPorLote() + ", " + lote.getQtdEstoque() + ")");
	}
	
	/* (non-Javadoc)
	 * @see drugdealer.persistencia.IEstoquePersistence#update(drugdealer.dados.Lote)
	 */
	public void update(Lote lote) throws DoesNotExistsException {
		SimpleDateFormat formato = new SimpleDateFormat("dd/MM/yyyy");
		super.update("UPDATE " + EstoquePersistence.TABLENAME + " SET " +
				"cod_remedio=" + lote.getRemedio().getCodigo() + "," +
				"datafabricacao='" + formato.format(lote.getDataFabricacao()) +
				"', datavalidade='" + formato.format(lote.getDataValidade()) +
				"', qtdestoque=" + lote.getQtdEstoque() + ", " +
				"qtdporlote=" + lote.getQtdPorLote() +
				" WHERE " + EstoquePersistence.PRIMARYKEY + "=" +
				lote.getCodigo());
	}

	/* (non-Javadoc)
	 * @see drugdealer.persistencia.IEstoquePersistence#remove(drugdealer.dados.Lote)
	 */
	public void remove(Lote lote) {
		super.remove(EstoquePersistence.TABLENAME, 
				EstoquePersistence.PRIMARYKEY, lote.getCodigo());
	}
}
